﻿SELECT 
D.AREA_MGR, --1
D.ACCT_MGR, --2
COALESCE(E.CNT, 0) MTD_CNT, --3
COALESCE(J.CNT, 0) PREV_MTD_CNT,--4
COALESCE(H.CNT, 0) PREV_MON_CNT, --5
MTD_CNT* (G.LAST_DAY/F.DOM) PACING,--6
CASE WHEN PREV_MON_CNT=0 THEN 0 ELSE CAST(PACING AS DECIMAL(20,4))/PREV_MON_CNT END PACE_RATE,--7
RANK(MTD_CNT) ACCT_MGR_RANK,--8
K.AREA_MGR_RANK--9 
FROM 
(SELECT AREA_MGR, ACCT_MGR FROM MIS.ACC_BASE_DATA WHERE ACCT_MGR GT '' 	GROUP BY 1,2) 
D
INNER JOIN  
(
   SELECT AREA_MGR, 
   CNT, 
   RANK(CNT) AREA_MGR_RANK 
   FROM 
   (
		SELECT 
		B.AREA_MGR, 
		COUNT(*) CNT
		FROM 
		MIS.ACC_RAW 
		A
		INNER JOIN 
		MIS.ACC_BASE_DATA 
		B 
		ON A.TRAC_ID=B.TRAC_ID,
		(
			SELECT 
			CALENDAR_DATE-DAY_OF_MONTH+1 BOM, 
			CALENDAR_DATE EOM 
			FROM 
			SYS_CALENDAR.CALENDAR 
			WHERE 
			CALENDAR_DATE=CURRENT_DATE
		) 
		C
		WHERE 
		A.START_DT BETWEEN C.BOM AND C.EOM
		GROUP BY 1
	) 
	Y
) 
K 
ON D.AREA_MGR=K.AREA_MGR
LEFT OUTER JOIN 
(
   SELECT 
   B.AREA_MGR, 
   B.ACCT_MGR, 
   COUNT(*) CNT
   FROM MIS.ACC_RAW 
   A
   INNER JOIN 
   MIS.ACC_BASE_DATA 
   B 
   ON A.TRAC_ID=B.TRAC_ID,
   (
		SELECT 
		CALENDAR_DATE-DAY_OF_MONTH+1 BOM, 
		CALENDAR_DATE EOM 
		FROM SYS_CALENDAR.CALENDAR 
		WHERE CALENDAR_DATE=CURRENT_DATE
	) 
	C
	WHERE 
	A.START_DT BETWEEN C.BOM AND C.EOM
	GROUP BY 1,2
) 
E 
ON 
D.AREA_MGR=E.AREA_MGR 
AND D.ACCT_MGR=E.ACCT_MGR
LEFT OUTER JOIN 
(
	SELECT B.AREA_MGR, B.ACCT_MGR, COUNT(*) CNT
	FROM MIS.ACC_RAW 
	A
    INNER JOIN MIS.ACC_BASE_DATA 
	B 
	ON 
	A.TRAC_ID=B.TRAC_ID,
	(
		SELECT 
		CALENDAR_DATE-DAY_OF_MONTH+1 BOM, 
		CALENDAR_DATE EOM FROM SYS_CALENDAR.CALENDAR 
		WHERE 
		CALENDAR_DATE=ADD_MONTHS(CURRENT_DATE,-1)
	) 
	C
	WHERE 
	A.START_DT BETWEEN C.BOM AND C.EOM
	GROUP BY 1,2
) 
J 
ON D.AREA_MGR=J.AREA_MGR 
AND D.ACCT_MGR=J.ACCT_MGR
LEFT OUTER JOIN 
(
	SELECT B.AREA_MGR, B.ACCT_MGR, COUNT(*) CNT
	FROM MIS.ACC_RAW 
	A
    INNER JOIN MIS.ACC_BASE_DATA 
	B 
	ON A.TRAC_ID=B.TRAC_ID,
	(
		SELECT 
		ADD_MONTHS(CALENDAR_DATE-DAY_OF_MONTH+1,-1) BOM, 
		CALENDAR_DATE-DAY_OF_MONTH EOM 
		FROM 
		SYS_CALENDAR.CALENDAR 
		WHERE
		CALENDAR_DATE=CURRENT_DATE
	)
	C
	WHERE 
	A.START_DT BETWEEN C.BOM AND C.EOM
	GROUP BY 1,2
) 
H 
ON D.AREA_MGR=H.AREA_MGR 
AND D.ACCT_MGR=H.ACCT_MGR,
(
	SELECT DAY_OF_MONTH DOM 
	FROM SYS_CALENDAR.CALENDAR 
	WHERE CALENDAR_DATE=CURRENT_DATE
) 
F,
(
	SELECT 
	MAX(DAY_OF_MONTH) LAST_DAY 
	FROM SYS_CALENDAR.CALENDAR 
	WHERE 
	MONTH_OF_CALENDAR=
	(
		SELECT MONTH_OF_CALENDAR 
		FROM 
		SYS_CALENDAR.CALENDAR 
		WHERE
		CALENDAR_DATE=CURRENT_DATE
) 
G
ORDER BY 9,8
;